#-*- coding:utf-8 -*-
import MySQLdb
from schema import DatabaseSchema
from data_row import DataRow, DataRowCollection
from weakref import ref

class Cursor(object):
    def __init__(self, db_cursor, conn):
        self._cursor = db_cursor
        self._conn = ref(conn)
        self._pool = None
        self._closed = False
    
    def __del__(self):
        self.close()
        
    def close(self):
        if not self._closed:
            if self._cursor:
                self._cursor.close()
            if self._conn:
                conn = self._conn
                self._conn = None
                if self._pool:
                    self._pool.release(conn)
        self._closed = True
    
    def __enter__(self):
        return self
    
    def __exit__(self, typ, val, tb):
        self.close()
    
    def literal(self, params):
        return self._cursor.connection.literal(params)

    def autocommit(self, value = True):
        self._cursor.connection.autocommit(value)

    def execute(self, sql, params = ()):
        while self._cursor.nextset(): pass
        if params:
            return self._cursor.execute(sql, params)
        else:
            return self._cursor.execute(sql)
    
    def fetchone(self, sql = None, params = ()):
        if sql:
            self.execute(sql, params)
        rs = self._cursor.fetchone()
        if rs:
            desc = self._cursor.description
            column_map = dict((d[0], i) for i, d in enumerate(desc))
            return DataRow(rs, column_map)
        return rs
    
    def fetchall(self, sql = None, params = ()):
        if sql:
            self.execute(sql, params)
        rs = self._cursor.fetchall()
        if rs:
            desc = self._cursor.description
            column_map = dict((d[0], i) for i, d in enumerate(desc))
            result = DataRowCollection(rs, column_map)
            return result
        return rs
    
    def fetch_objects(self, table, where=None, columns=None, params=(), 
                      source_result=False):
        model_class = None
        if isinstance(table, basestring):
            table_schema = DatabaseSchema.tables[table]
            model_class = table_schema.model_class
        else:
            model_class = table
            table = model_class.table_schema.table_name
        if not columns:
            columns = model_class.table_schema.fields.keys()
        if not where:
            where = ''
        if params:
            where = where % self.literal(params)
        sql = 'select %s from %s %s' % (','.join(columns), table, where)
        rs = self.fetchall(sql)
        if source_result: # 原始结果
            return rs
        else:
            return self._db_result2obj(rs, columns, model_class)
        
    def delete(self, tablename, where, params):
        if not params:
            raise MySQLdb.ProgrammingError('params is required')
        sql = 'delete from %s %s' % (tablename, where)
        return self.execute(sql, params)
        
    def update(self, obj, where=None, params=(), table=None, columns=[]):
        model_class, table = self._check_param(obj, table)
        values = []
        is_dict = isinstance(obj, dict)
        if not where:
            wherelist = []
            pk, pkvals = self._get_pk_values(model_class, obj)
            if len(pk) != len(pkvals):
                raise MySQLdb.ProgrammingError('Primary key is required')
            for name in pk:
                wherelist.append('%s=%%s' % name)
            where = ','.join(wherelist)
        else:
            pk, pkvals = (), ()
        if where.strip().lower().startswith('where'):
            where = where[5:]
        set_clause = []
        if not columns:
            if is_dict:
                columns = obj.keys()
            else:
                columns = obj.dirty_fields
        if not columns:
            return 0
        for k in columns:
            if is_dict:
                v = obj[k]
            else:
                v = getattr(obj, k)
            if not k in pk:
                set_clause.append('%s=%%s' % k)
                values.append(v)
        if params:
            values.extend(params)
        elif pkvals:
            values.extend(pkvals)
        sql = 'update %s set %s where %s' % (table, ','.join(set_clause), 
                                           where)
        return self.execute(sql, values)
    
    def insert(self, obj, table = None):
        model_class, table = self._check_param(obj, table)        
        dictObj = self._to_dict(obj)
        columns = ''
        values = []
        valueHolder = ''
        for k, v in dictObj.items():
            columns += k + ','
            valueHolder += '%s,'
            values.append(v)
        if not columns:
            return 0
        sql = 'insert into %s (%s) values(%s)' % (table, columns[:-1], valueHolder[:-1])
        self.execute(sql, values)
        lastId = self._cursor.lastrowid
        if lastId:
            pk = model_class.table_schema.primary_key[0]
            if type(obj) == dict:
                obj[pk.name] = lastId
            else:
                setattr(obj, pk.name, lastId)
        return lastId
            
    def save_object(self, obj, table = None):
        model_class, table = self._check_param(obj, table)
        pk, pkValues = self._get_pk_values(model_class, obj)
        if pkValues:
            condition = ''
            for pItem in pk:
                if condition:
                    condition += 'and '
                condition += pItem + '=%s '
            sql = 'select 1 from %s where %s limit 1' % (table, condition)            
            if self.fetchone(sql, pkValues):
                condition = condition % self.literal(pkValues)
                return self.update(obj, condition, table=table)
        return self.insert(obj, table)
    
    def delete_by_pk(self, obj_or_tablename, pk):
        if not hasattr(pk, '__iter__'):
            pk = (pk,)
        if isinstance(obj_or_tablename, basestring):
            tablename = obj_or_tablename
            table_schema = DatabaseSchema.tables[tablename]
        else:
            table_schema = obj_or_tablename.table_schema
            tablename = table_schema.table_name
        primary_key = table_schema.primary_key
        where = []
        for k in primary_key:
            where.append('%s=%%s' % k.name)
        where_str = ''
        if where:
            where_str = 'where %s' % ' and '.join(where)
        sql = 'delete from %s %s limit 1' % (tablename, where_str)
        self.execute(sql, pk)
    
    def _get_pk_values(self, model_class, obj):
        primary_key = model_class.table_schema.primary_key
        pk, values = [], []
        is_dict = isinstance(obj, dict)
        for k in primary_key:
            k = k.name
            pk.append(k)
            v = obj[k] if is_dict else getattr(obj, k)
            values.append(v)
        return tuple(pk), tuple(values)

    def _to_dict(self, obj):
        if type(obj) == dict:
            return obj
        else:
            return obj.to_dict()

    def _db_result2obj(self, rs, columns, model_class):
        result = []
        columnCount = len(columns)
        for r in rs:
            obj = model_class()
            index = 0
            while index < columnCount:
                setattr(obj, columns[index], r[index])
                index += 1
            result.append(obj)
        return result
    
    def _check_param(self, obj, tablename=None):
        """check params
        return model_class, tablename"""
        model_class = None
        if not isinstance(obj, dict):
            model_class = obj.table_schema.model_class
            if not tablename:
                tablename = obj.table_schema.table_name
        elif isinstance(tablename, basestring):
            model_class = DatabaseSchema.tables[tablename].model_class
        else:
            msg = 'tablename is required while obj is dict'
            raise MySQLdb.ProgrammingError(msg)
        return model_class, tablename
    
    def _add2pool(self, pool):
        self._pool = pool
        self._conn = self._conn()
    
    def __getattr__(self, name):
        return getattr(self._cursor, name)
    